﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace MsDbToSql
{
    public class DbOprator
    {
        #region 属性
        public string Server { get; set; }
        public string LoginName { get; set; }
        public string Password { get; set; }
        public string DataBase { get; set; }
        #endregion

        #region 构造函数
        /// <summary>
        /// 默认构造，属性值为“server=.;database=master;uid=sa;pwd=sa;”
        /// </summary>
        public DbOprator()
        {
            this.Server = ".";
            this.LoginName = "sa";
            this.Password = "sa";
            this.DataBase = "master";
        }

        /// <summary>
        /// 连接字符串属性值为“server=地址;database=master;uid=用户名;pwd=密码;”
        /// </summary>
        /// <param name="server"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        public DbOprator(string server, string uid, string pwd)
        {
            this.Server = server;
            this.LoginName = uid;
            this.Password = pwd;
            this.DataBase = "master";
        }
        /// <summary>
        /// 连接字符串完全自己定义
        /// </summary>
        /// <param name="server"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <param name="dbname"></param>
        public DbOprator(string server,string uid,string pwd, string dbname)
        {
            this.Server = server;
            this.LoginName = uid;
            this.Password = pwd;
            this.DataBase = dbname;
        }

        #endregion

        #region 生成表
        /// <summary>
        /// 生成连接字符串
        /// </summary>
        /// <returns></returns>
        private string GetConnString()
        {
            string str = string.Format(@"Data Source={0};Initial Catalog={3};Persist Security Info=True;User ID={1};Password={2}",
                this.Server, this.LoginName, this.Password, this.DataBase);
            return str;
        }

        private string GetMasterConnString()
        {
            string str = string.Format(@"Data Source={0};Initial Catalog={3};Persist Security Info=True;User ID={1};Password={2}",
                this.Server, this.LoginName, this.Password, "master");
            return str;
        }

        //检测数据库连接
        public bool CheckLink()
        {
            SqlConnection conn = new SqlConnection(GetConnString());
            try
            {
                conn.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 获取服务器上所有数据库
        /// </summary>
        /// <returns></returns>
        public  List<string> GetAllDataBase()
        {
            string str = GetMasterConnString();
            string sql = "select name from sysdatabases";
            SQLHelper.ConnString = str;
            List<string> list = new List<string>();
            SqlDataReader reader = null;
            try
            {
                reader = SQLHelper.ExcuteReader(sql);
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 获取指定数据库中所有的表
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllTables()
        {
            SQLHelper.ConnString = GetConnString();
            string sql = "select name from sysobjects where type='U' and name<>'sysdiagrams'";
            List<string> list = new List<string>();
            try
            {
                SqlDataReader reader = SQLHelper.ExcuteReader(sql);
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据表名生成创建表的sql语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dropTable">是否生成删除表的语句，默认生成</param>
        /// <returns>生成表的sql语句，不包括约束</returns>
        public string CreateTable(string tableName,bool dropTable=true)
        {
            string sql = string.Format(@"select field=c.name,type=t.name,length=case 
                                                    when t.name = 'nvarchar' then c.length / 2
                                                    when t.name = 'nchar' then c.length / 2
									                else c.length
                                                    end,isnullable,isIdentity=case
									                    when c.status=128 then 1
									                    else 0
								                        end,numLen=c.xprec,dotLen=c.xscale
                                       from syscolumns as c inner join systypes as t on c.xtype = t.xtype
                                       where id = object_id('{0}') and t.status = 0", tableName);
            DataTable dt = SQLHelper.ExcuteTable(sql);
            StringBuilder sb = new StringBuilder();
            if (dropTable)  //生成删除数据库的语句
            {
                sb.AppendLine("if exists(select * from sysobjects where name='[" + tableName + "]')");
                sb.AppendLine("   drop table ["+tableName+"]");
                sb.AppendLine("go");
            }
            sb.AppendLine("create table [" + tableName + "] (");
            foreach (DataRow row in dt.Rows)
            {
                sb.AppendLine(getFieldInfoSql(row["field"].ToString(),row["type"].ToString(),row["length"].ToString(),row["isnullable"].ToString(),row["isIdentity"].ToString(),row["numLen"].ToString(),row["dotLen"].ToString(),tableName));
            }
            sb.Remove(sb.ToString().LastIndexOf(','), 1);  //移除最后以“，”
            sb.AppendLine(")");
            sb.AppendLine("go");
            return sb.ToString();
        }

        /// <summary>
        /// 生成表的约束sql语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string CreateConstrain(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("--"+tableName+"表约束");
            string sql = string.Format("exec sp_helpconstraint '{0}'",tableName);
            DataTable dt= SQLHelper.ExcuteDataSet(sql).Tables[1];
            string cname, ctype, remark="",temp,tempkey;
            for (int i= 0;i<dt.Rows.Count;i++)
            {
                cname = dt.Rows[i]["constraint_name"].ToString(); //约束名称
                ctype = GetConstraintType(dt.Rows[i]["constraint_type"].ToString()); //约束类型
                if (ctype == "CHECK") //约束说明
                    remark = string.Format("{0}", dt.Rows[i]["constraint_keys"]);
                else if (ctype == "DEFAULT")
                {
                    temp = dt.Rows[i]["constraint_type"].ToString();
                    remark = string.Format("{0} for {1}", dt.Rows[i]["constraint_keys"], temp.Substring(temp.LastIndexOf(' ')+1));
                }
                else if (ctype== "FOREIGN KEY")
                {
                    temp = dt.Rows[i]["constraint_keys"].ToString();
                    i++;
                    tempkey = dt.Rows[i]["constraint_keys"].ToString();
                    remark = string.Format(" ({0}) REFERENCES {1}", temp, tempkey.Substring(tempkey.LastIndexOf('.') +1));
                }else if (ctype== "PRIMARY KEY")
                {
                    remark = "(" + dt.Rows[i]["constraint_keys"] + ")";
                }

                sb.AppendLine("Alter Table " + tableName);
                sb.AppendFormat("   Add Constraint {0} {1}{2}\n",cname,ctype,remark);
            }
            return sb.ToString();
        }

        private string GetConstraintType(string dbInfo)
        {
            string prev= dbInfo.Substring(0, dbInfo.IndexOf(' '));
            string result = prev;
            switch(prev)
            {
                case "FOREIGN":
                    result = "FOREIGN KEY";
                    break;
                case "PRIMARY":
                    result = "PRIMARY KEY";
                    break;
            }
            return result;
        }

        /// <summary>
        /// 获取生成一个字段的语句
        /// </summary>
        /// <param name="field">字段名</param>
        /// <param name="type">字段类型</param>
        /// <param name="length">字段长度</param>
        /// <param name="isnullable">是否允许为空，1为允许，0为不允许</param>
        /// <param name="isIdentity">是否为标识列 1为是，0为否</param>
        /// <param name="numLen">当数据类型为decimal或numeric时的总长度</param>
        /// <param name="dotLen">当数据类型为decimal或numeric时的小数长度</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns>格式： 字段名 类型 not null identity(1,1),</returns>
        private string getFieldInfoSql(string field,string type,string length,string isnullable, string isIdentity,string numLen,string dotLen,string tableName)
        {
            string fieldStr = "  [" + field + "] " + getTypeStr(type, length, numLen, dotLen);
            if (isIdentity=="1")
            {
                fieldStr += " Identity("+getIdentitySeedAndIncr(tableName)+")";
            }
            if (isnullable == "0")
                fieldStr += " not null";
            return fieldStr+",";    
        }

        /// <summary>
        /// 获取字段类型字符串
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="length">长度</param>
        /// <param name="numLen">当数据类型为decimal或numeric时的总长度</param>
        /// <param name="dotLen">当数据类型为decimal或numeric时的小数长度</param>
        /// <returns></returns>
        private string getTypeStr(string type,string length,string numLen,string dotLen)
        {
            if (type == "char" || type == "varchar" || type == "nchar" || type == "nvarchar"
                || type == "binary" || type == "varbinary")
            {
                return (type + "(" + length + ")").Replace("-1","MAX");  //如果类型为varchar(max)，其长度为-1
            }
            else if (type== "decimal" || type== "numeric")
            {
                return type + "(" +numLen+","+dotLen+ ")";
            }
            else
                return type;
        }

        /// <summary>
        /// 获取标识种子和标识增量字符串
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <returns>格式：“种子，增量”</returns>
        private string getIdentitySeedAndIncr(string tableName)
        {
            string sql = string.Format("SELECT IDENT_SEED ('{0}'),IDENT_INCR('{0}')", tableName);
            SqlDataReader reader = SQLHelper.ExcuteReader(sql);
            string result = "1,1";
            if (reader.Read())
            {
                result= reader[0] + "," + reader[1];
            }
            reader.Close();
            return result;
        }

        #endregion

        #region 生成数据(insert)语句

        /// <summary>
        /// 生成表的insert语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GenerateInsert(string tableName)
        {
            string fields = GetFields(tableName);
            string sql = string.Format("select {0} from {1}", fields,tableName);
            StringBuilder insert = new StringBuilder();
            insert.AppendLine("--" + tableName + "表数据");
            try
            {
                SqlDataReader reader = SQLHelper.ExcuteReader(sql);
                if (reader.HasRows)
                {
                    string line ;
                    StringBuilder values = new StringBuilder();
                    while (reader.Read())
                    {
                        values.Clear();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            values.Append( "'").Append(reader[i].ToString()).Append("',");
                        }
                        values.Remove(values.ToString().LastIndexOf(','), 1);
                        line = string.Format("insert into {0}({1}) values({2})", tableName, fields,values);
                        insert.AppendLine(line);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return insert.AppendLine("go").ToString();
        }

        /// <summary>
        /// 获取表的字段，标识列除外
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private string GetFields(string tableName)
        {
            string sql = string.Format("select name,status from syscolumns where id = object_id('{0}') order by colorder", tableName);
            SqlDataReader reader = SQLHelper.ExcuteReader(sql);
            StringBuilder fields = new StringBuilder();
            while(reader.Read())
            {
                if (reader["status"].ToString() == "128")  //如果是标识列
                    continue;
                fields.Append("[" + reader["name"].ToString() + "],");
            }
            reader.Close();
            fields.Remove(fields.ToString().LastIndexOf(','), 1);
            return fields.ToString();
        }

       
        #endregion

    }
}
